from django.test import TestCase
import datetime
import pymysql
from collections import defaultdict
# Create your tests here.

# 创建MySQL连接
mysql_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8')
cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)

# 获取当前时间
cur_time = datetime.date.today().isoformat()
ago_time = (datetime.date.today() + datetime.timedelta(days=-1)).isoformat()

# 清除昨天的热门
sql = "delete from m2_t_search"
cursor.execute(sql)
mysql_conn.commit()

names = ['电影搜索推荐', '类别搜索推荐']
for types in (0, 1):
    name = names[types]

    # 查询今日最热门的电影ID和图片ID
    sql_star = "select media_id id, count(*) score from user_t_star where type = {types} and time between '{ago}' and '{cur}' group by media_id".format(
        types=types, ago=ago_time, cur=cur_time)
    sql_parise = "select media_id id, count(*)*3 score from user_t_parise where type = {types} and time between '{ago}' and '{cur}' group by media_id".format(
        types=types, ago=ago_time, cur=cur_time)
    info = defaultdict(int)
    try:
        cursor.execute(sql_star)
        star_info = cursor.fetchall()
        for each in star_info:
            info[each['id']] += each['score']

        cursor.execute(sql_parise)
        parise_info = cursor.fetchall()
        for each in parise_info:
            info[each['id']] += each['score']
    except:
        print('查询今日最热门的电影ID和图片ID')
        print("更新" + name + '失败！')

    # 对电影和图片做不同的处理
    targets_score = defaultdict(int)
    if types:  # 图集
        for picture_id, score in info.items():
            sql = "select target_id from picture_t_picture_class where picture_id=%d" % picture_id

            targets = []
            try:
                cursor.execute(sql)
                targets = cursor.fetchall()
            except:
                print(sql)

            for target in targets:
                targets_score[target['target_id']] += score
    else:  # 电影
        targets_score = info

    targets_score_5 = dict(sorted(targets_score.items(), key=lambda x: x[1], reverse=True)[:5])

    # 将今天的热门加入表中
    sql = ""
    for media_id in targets_score_5.keys():
        sql += "insert into m2_t_search (media_id, type) values (%s, %d);" % (media_id, types)

    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except:
        print(sql)
        print('更新' + name + '失败！')
